package com.lap.generator.infrastructure.query.strategy;

import com.lap.generator.commons.enums.NullType;
import com.lap.generator.infrastructure.query.JavaTypeMapper;
import com.lap.generator.infrastructure.query.meta.ColumnMeta;
import com.lap.generator.infrastructure.query.meta.TableMeta;
import java.sql.*;
import java.util.*;

public class MySQLMetadataDialect implements MetadataDialect {
  private static final String PRIMARY_KEY_FLAG = "PRI";

  @Override
  public List<TableMeta> readTableMeta(Connection connection, List<String> includeTables)
      throws SQLException {
    String dbName = connection.getCatalog();
    List<TableMeta> tables = new ArrayList<>();

    for (String tableName : includeTables) {
      TableMeta tableMeta = buildTableMeta(connection, dbName, tableName);
      tables.add(tableMeta);
    }

    return tables;
  }

  private TableMeta buildTableMeta(Connection conn, String dbName, String tableName)
      throws SQLException {
    TableMeta table = new TableMeta();
    table.setTableName(tableName);
    table.setTableComment(fetchTableComment(conn, dbName, tableName));

    List<ColumnMeta> columns = fetchColumns(conn, dbName, tableName);
    table.setColumns(columns);

    columns.stream()
        .filter(ColumnMeta::isPrimaryKey)
        .findFirst()
        .ifPresent(
            pk -> {
              table.setPrimaryKey(pk.getColumnName());
              table.setPrimaryKeyJavaType(pk.getModelType().javaType().getCode());
            });

    return table;
  }

  private String fetchTableComment(Connection conn, String dbName, String tableName)
      throws SQLException {
    String sql =
        """
      SELECT TABLE_COMMENT
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
    """;

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
      ps.setString(1, dbName);
      ps.setString(2, tableName);
      ResultSet rs = ps.executeQuery();
      return rs.next() ? rs.getString("TABLE_COMMENT") : "";
    }
  }

  private List<ColumnMeta> fetchColumns(Connection conn, String dbName, String tableName)
      throws SQLException {
    String sql =
        """
      SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY,
        COLUMN_COMMENT,CHARACTER_MAXIMUM_LENGTH,
        IS_NULLABLE
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
      ORDER BY ORDINAL_POSITION
    """;

    List<ColumnMeta> columns = new ArrayList<>();
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
      ps.setString(1, dbName);
      ps.setString(2, tableName);
      ResultSet rs = ps.executeQuery();

      while (rs.next()) {
        columns.add(resolverColumnMeta(rs));
      }
    }
    return columns;
  }

  private ColumnMeta resolverColumnMeta(ResultSet rs) throws SQLException {
    ColumnMeta col = new ColumnMeta();
    col.setColumnName(rs.getString("COLUMN_NAME"));
    col.setDataType(rs.getString("DATA_TYPE"));
    col.setModelType(
        JavaTypeMapper.map(rs.getString("DATA_TYPE"), rs.getString("CHARACTER_MAXIMUM_LENGTH")));
    col.setNullable(NullType.isNull(rs.getString("IS_NULLABLE")));
    col.setMaxLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
    col.setComment(Optional.ofNullable(rs.getString("COLUMN_COMMENT")).orElse(""));
    col.setPrimaryKey(PRIMARY_KEY_FLAG.equalsIgnoreCase(rs.getString("COLUMN_KEY")));
    return col;
  }
}
